//
//  SqliteManager.swift
//  weiBo项目准备
//
//  Created by 鲜世杰 on 16/5/24.
//  Copyright © 2016年 鲜世杰. All rights reserved.
//

import UIKit

let dbName = "sinaWeibo.db"
class SqliteManager: NSObject {
    
    static let manager : SqliteManager = SqliteManager()
    
    var queue : FMDatabaseQueue?
    private override init() {

        super.init()

        
        let dbPath = (NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)
        queue = FMDatabaseQueue(path: dbPath)
        
        
        
    }
    
    private func createTable () {
    
        
        let sqlPath = NSBundle.mainBundle().pathForResource("db.sql", ofType: nil)!
        
        let sql = try! String(contentsOfFile: sqlPath)
        
        queue?.inDatabase({ (db) -> Void in
            let result = db.executeStatements(sql)
            if result {
            
                print("创建表成功")
                
            }else {
            
                print("创建失败")
                
            }
            
            
        })
        
    }
    
    
    func insert() {
    
        let sql = "INSERT INTO T_PERSON(NAME, AGE) VALUES(?,?)"

        queue?.inDatabase({ (db) -> Void in
            let result = db.executeUpdate(sql, withArgumentsInArray: ["霍建华",32])
            
            if result {
            
                print("插入成功")
                
            }else {
                
                print("插入失败")
                
            }
            
        })
        
        
        
    }
    
    func select() {
    
        let sql = "SELECT ID, NAME, AGE FROM T_PERSON"

        queue?.inDatabase({ (db) -> Void in
            let resultSet = db.executeQuery(sql, withArgumentsInArray: nil)
            
            while resultSet.next() {
            
                let id = resultSet.intForColumn("ID")
                let name = resultSet.intForColumn("NAME")
                let age = resultSet.intForColumn("AGE")
                print("id = \(id), name = \(name), age = \(age)")


                
            }
        })
        
    }
    func select2() {
    
        let sql = "SELECT ID, NAME, AGE FROM T_PERSON"

        queue?.inDatabase({ (db) -> Void in
            let resultSet = db.executeQuery(sql, withArgumentsInArray: nil)
            
            while resultSet.next() {
                
                let id = resultSet.intForColumnIndex(0)
                let name = resultSet.intForColumnIndex(1)
                let age = resultSet.intForColumnIndex(2)
            print("id = \(id), name = \(name), age = \(age)")
            }
        })
        
    }
    
    func select3() {
    
        let sql = "SELECT ID, NAME, AGE FROM T_PERSON"
        queue?.inDatabase({ (db) -> Void in
            
            
            let resultSet = db.executeQuery(sql, withArgumentsInArray: nil)
            while resultSet.next(){
                var dic : [String: AnyObject] = [String : AnyObject]()
                for i in 0..<resultSet.columnCount() {
                
                    let colName = resultSet.columnNameForIndex(i)
                    
                    let colValue = resultSet.objectForColumnIndex(i)

                    dic[colName] = colValue

                }
                print(dic)

            }
            
            
        })
        
    }
    
    func queryResultSetWithSql(sql: String) -> [[String: AnyObject]] {
        
       
        var tempArray = [[String: AnyObject]]()
        
        queue?.inDatabase({ (db) -> Void in
            let resultSet = db.executeQuery(sql, withArgumentsInArray: nil)
            
            while resultSet.next() {
                
                var dic = [String: AnyObject]()
                
                for i in 0..<resultSet.columnCount() {
                   
                    let colName = resultSet.columnNameForIndex(i)
                    
                    let colValue = resultSet.objectForColumnIndex(i)
                    
                   
                    dic[colName] = colValue
                    
                }
                
                tempArray.append(dic)

            }
            
        })
        return tempArray
    }
    
    func update() {
        
        let sql = "UPDATE T_PERSON SET NAME = ?, AGE = ? WHERE ID = ?"
        
        queue?.inDatabase({ (db) -> Void in
            let result = db.executeUpdate(sql, withArgumentsInArray: ["古天乐", 20, 2])
            if result {
                print("修改成功")
            } else {
                print("修改失败")
            }
        })
    }
    
    
    func delete() {
        
        let sql = "DELETE FROM T_PERSON WHERE ID = ?"
        
        queue?.inDatabase({ (db) -> Void in
            db.executeUpdate(sql, withArgumentsInArray: [4])
        })
    }

    
}
